package fr.inserm.u674.chcdbweb.model.DAO;

import static fr.inserm.u674.chcdbweb.model.DAO.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import fr.inserm.u674.chcdbweb.model.DTO.EAVModality;
import fr.inserm.u674.chcdbweb.model.DTO.EAVVariable;
import fr.inserm.u674.chcdbweb.model.DTO.Tissue;

/**
 * This class represents a SQL Database Access Object for the {@link Tissue} DTO. This DAO should be
 * used as a central point for the mapping between the User DTO and a SQL database.
 * 
 * @author BalusC, Thomas Burguiere
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
/**
 * @author Thomas Burguiere
 * 
 */
public final class TissueDAO {
	// Constants
	// ----------------------------------------------------------------------------------

	private static final String SQL_FIND_BY_CHCID_AND_TISSUETYPEID =
		"SELECT * FROM Tissue WHERE chcID = ? AND tissueTypeID = ?";

	private static final String SQL_LIST = "SELECT * FROM Tissue ORDER BY chcID, TissueTypeID";
	// private static final String SQL_LIST_WITH_EAV_DATA_VIEW = "SELECT * FROM view_eavdata "
	// + " ORDER BY chcID, TissueTypeID";

	private static final String SQL_EXISTS_TISSUETYPEID =
		"SELECT COUNT(*) FROM Tissue WHERE tissueTypeID = ?";
	private static final String SQL_EXISTS_CHCID = "SELECT COUNT(*) FROM Tissue WHERE chcID = ?";
	private static final String SQL_EXISTS =
		"SELECT COUNT(*) FROM Tissue WHERE chcID = ? AND tissueTypeID = ?";

	private static final String SQL_GET_TISSUE_QUANTITY =
		"SELECT quantityTissueLeftAfterManipulation, manipulationDate "
			+ "FROM Tissue t INNER JOIN TissueManipulation tm "
			+ "ON (t.chcID = tm.tissue_chcID AND t.tissueTypeID = tm.tissue_tissueTypeID ) "
			+ "WHERE t.chcID = ? AND t.tissueTypeID = ? ORDER BY manipulationDate ASC ";

	private static final String SQL_LIST_NONMASTER_ASSOCIATED_TISSUES = "SELECT * FROM Tissue "
		+ "WHERE (masterTissue_chcID = ? AND masterTissue_tissueTypeID = ?) ";
	// + "OR (chcID = ? and tissueTypeID = ?) ";

	private static final String SQL_LIST_MASTER_ASSOCIATED_TISSUES = "SELECT * FROM Tissue "
		+ "WHERE masterTissue_chcID = ? AND masterTissue_tissueTypeID = ?";

	private static final String SQL_FETCH_TISSUE_EAV_DATA =
		"SELECT * FROM EAV_Modalities INNER JOIN EAV_Variables ON "
			+ "(EAV_Modalities.eav_Variables_variableID " + "= EAV_Variables.variableID) "
			+ "WHERE tissue_chcID = ? and tissue_tissueTypeID = ? ORDER BY variableName";

	private static final String SQL_INSERT_MASTERTISSUE =
		"INSERT INTO Tissue (chcID, tissueTypeID) VALUES (?,?)";

	private static final String SQL_INSERT_NONMASTER_TISSUE =
		"INSERT INTO Tissue (chcID, tissueTypeID,masterTissue_chcID,masterTissue_tissueTypeID)"
			+ " VALUES (?,?,?,?)";

	private static final String SQL_UPDATE_TISSUE =
		"UPDATE tissue SET masterTissue_chcID = ?, masterTissue_TissueTypeID = ?, tissueComment = ? ,"
			+ " patient_patientID= ?, histologicDiagnosis_HistologicDiagnosisID=? "
			+ " WHERE chcID = ? AND tissueTypeID = ?";

	private static final String SQL_DELETE_TISSUE =
		"DELETE FROM Tissue WHERE chcID=? AND tissueTypeID = ?";

	private static final String SQL_MASTERTISSUE_DISSOCIATE =
		"UPDATE Tissue SET masterTissue_chcID = NULL, masterTissue_TissueTypeID = NULL "
			+ "WHERE masterTissue_chcID = ? AND masterTissue_TissueTypeID = ?";

	// ----------------- DYNAMIC EAV QUERY --------------------------------------------------------
	private static final String SQL_LIST_WITH_EAV_DATA_DYNAMIC_SELECT_STUB = "SELECT Tissue.* ";
	private static final String SQL_LIST_WITH_EAV_DATA_DYNAMIC_FROM_STUB = "FROM Tissue ";
	private static final String SQL_LIST_WITH_EAV_DATA_DYNAMIC_GROUPBY_STUB =
		"GROUP BY chcid, tissuetypeID ";

	private static final String SQL_LIST_WITH_EAV_DATA_DYNAMIC2_SELECT_STUB = "SELECT"
		+ " Tissue.*";
	// TODO FIND by CHCID

	// Vars
	// --------------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	// Constructors
	// --------------------------------------------------------------------------------------------

	/**
	 * Construct aa Tissue DAO for the given DAOFactory. Package private so that it can be
	 * constructed inside the DAO package only.
	 * 
	 * @param daoFactory
	 *            The DAOFactory to construct this Tissue DAO for.
	 */
	TissueDAO(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// Actions
	// ------------------------------------------------------------------------------------

	/**
	 * Returns the tissue from the database matching the given chcID and tissueTypeID, otherwise
	 * null.
	 * 
	 * @param chcID
	 *            The chcID of the tissue to be returned.
	 * @param id
	 *            The tisueTypeID of the tissue to be returned.
	 * @return The tissue from the database matching the given chcID and tissueTypeID, otherwise
	 *         null.
	 * @throws DAOException
	 *             If something fails at database level.
	 */
	public Tissue find(Integer chcID, String tissueTypeID) throws DAOException {
		return find(SQL_FIND_BY_CHCID_AND_TISSUETYPEID, chcID, tissueTypeID);
	}

	public Tissue find(String rawCHCID) throws DAOException {
		if (Tissue.isRawCHCIDValid(rawCHCID)) {
			Tissue t = new Tissue(rawCHCID);
			return find(t.getChcID(), t.getTissueTypeID());
		}
		else
			throw new DAOException("The raw chcid provided is not valid.");
	}

	/**
	 * Returns the tissue from the database matching the given SQL query and query parameters
	 * 
	 * @param sql
	 *            the SQL query which is to be submitted to the database
	 * @param values
	 *            an array of objects which are to be used as parameters in the SQL query
	 * @return the tissue from the database that matches the given SQL query and parameter
	 * @throws DAOException
	 */
	private Tissue find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Tissue tissue = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				tissue = mapTissue(resultSet);
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}

		return tissue;
	}

	/**
	 * Returns a list of all tissues from the database ordered by chcID and tissueTypeID. The list
	 * is never null and is empty when the database does not contain any tissue.
	 * 
	 * @return A list of all tissues from the database ordered by chcID and tissueTypeID.
	 * @throws DAOException
	 *             If something fails at database level.
	 */
	public List<Tissue> list() throws DAOException {
		ArrayList<Tissue> list = new ArrayList<Tissue>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = connection.prepareStatement(SQL_LIST);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				list.add(mapTissue(resultSet));
			}

		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return list;
	}

	/**
	 * Returns a list of all tissues from the database ordered by chcID and tissueTypeID. The list
	 * is never null and is empty when the database does not contain any tissue.
	 * 
	 * @param getQuantityLeft
	 *            a boolean that indicates if the method should fetch the quantity left for each
	 *            tissue
	 * @param eavVariables
	 *            a list of the EAV variables that should be retrieved for each tissue.
	 * @param tissuesOfInterest
	 *            a restrictive list of the Tissues that are to be fetched, Tissues not in this list
	 *            will be ignored
	 * @param showTissuesWithNoModalities
	 *            a Boolean that specifies wether the query should fetch tissues for which there are
	 *            no modalities for the variables specified in eavVariablesNames
	 * @return A list of all tissues from the database ordered by chcID and tissueTypeID.
	 * @throws DAOException
	 *             If something fails at database level.
	 */
	public List<Tissue> list(Boolean getQuantityLeft, String[] eavVariables,
		ArrayList<Tissue> tissuesOfInterest, Boolean showTissuesWithNoModalities)
		throws DAOException {
		ArrayList<String> eavVariablesNames = new ArrayList<String>();
		for (int i = 0; i < eavVariables.length; i++)
			eavVariablesNames.add(eavVariables[i]);

		return listWithDynamicEAVQuery2(getQuantityLeft, eavVariablesNames, tissuesOfInterest,
			showTissuesWithNoModalities);
	}

	/**
	 * Returns a list of tissues from the database (with data from the E.A.V.) ordered by chcID and
	 * tissueTypeID. The list is never null and is empty when the database does not contain any
	 * tissue.
	 * 
	 * @param getQuantityLeft
	 *            a boolean that indicates if the method should fetch the quantity left for each
	 *            tissue
	 * @param eavVariablesNames
	 *            a list of the EAV variables that should be retrieved for each tissue.
	 * @param tissuesOfInterest
	 *            a restrictive list of the Tissues that are to be fetched, Tissues not in this list
	 *            will be ignored
	 * @param showTissuesWithNoModalities
	 *            a Boolean that specifies wether the query should fetch tissues for which there are
	 *            no modalities for the variables specified in eavVariablesNames
	 * @return
	 * @throws DAOException
	 * @deprecated use {@link #listWithDynamicEAVQuery2(Boolean,ArrayList,ArrayList,Boolean)}
	 *             instead
	 */
	public List<Tissue> listWithDynamicEAVQuery(Boolean getQuantityLeft,
		ArrayList<String> eavVariablesNames, ArrayList<Tissue> tissuesOfInterest,
		Boolean showTissuesWithNoModalities) throws DAOException {

		String finalSQL = SQL_LIST_WITH_EAV_DATA_DYNAMIC_SELECT_STUB;
		int varCount = 1;
		for (String eavVarName : eavVariablesNames) {
			finalSQL += " , `mod" + varCount + "`.`modality` AS " + eavVarName + " \n ";
			varCount++;
		}

		if (getQuantityLeft)
			finalSQL +=
				", GROUP_CONCAT( " + "	CONCAT_WS(' on date : ',"
					+ "				TissueManipulation.quantityTissueLeftAfterManipulation  , "
					+ "				TissueManipulation.manipulationDate)"
					+ " ORDER BY manipulationDate DESC SEPARATOR ' / ') AS quantityLeft ";

		varCount = 1;
		finalSQL += SQL_LIST_WITH_EAV_DATA_DYNAMIC_FROM_STUB + " \n ";
		for (String eavVarName : eavVariablesNames) {
			finalSQL += "LEFT OUTER JOIN `EAV_Modalities` `mod" + varCount + "` ON ";
			finalSQL +=
				"(tissue.`chcID` = `mod" + varCount
					+ "`.tissue_chcID AND tissue.tissueTypeID = `mod" + varCount
					+ "`.tissue_tissueTypeID ";
			finalSQL += "AND `mod" + varCount + "`.eav_Variables_variableID = ";
			finalSQL +=
				"(SELECT variableID FROM `EAV_Variables` WHERE `variableName` = '" + eavVarName
					+ "')) \n ";
			varCount++;
		}

		if (getQuantityLeft)
			finalSQL +=
				"LEFT JOIN `TissueManipulation` ON "
					+ "(tissue.chcid = TissueManipulation.`tissue_chcID` "
					+ "	AND TissueManipulation.`tissue_tissueTypeID` = tissue.`tissueTypeID` "
					+ "	AND quantityTissueLeftAfterManipulation IS NOT NULL) ";

		if (varCount > 1 && !showTissuesWithNoModalities) {
			finalSQL += " WHERE NOT ( ";
			int i = 1;
			for (i = 1; i < varCount - 1; i++) {
				finalSQL += "`mod" + i + "`.`modality` IS NULL AND ";
			}
			finalSQL += "`mod" + i + "`.`modality` IS NULL ) ";
		}

		// filtering by specific tissues
		if (tissuesOfInterest != null && tissuesOfInterest.size() > 0) {
			if (showTissuesWithNoModalities)
				finalSQL += " WHERE ( ";
			else
				finalSQL += " AND ( ";
			int tissueCount = 0;
			for (Tissue tissueOfInterest : tissuesOfInterest) {

				finalSQL +=
					" (chcID = " + tissueOfInterest.getChcID() + " AND tissueTypeID = '"
						+ tissueOfInterest.getTissueTypeID() + "') ";
				if (tissueCount < tissuesOfInterest.size() - 1) finalSQL += " OR ";
				tissueCount++;
			}
			finalSQL += " ) ";
		}

		finalSQL += SQL_LIST_WITH_EAV_DATA_DYNAMIC_GROUPBY_STUB;

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Tissue> tissues = new ArrayList<Tissue>();
		boolean getEAVData = false;
		if (eavVariablesNames != null && eavVariablesNames.size() > 0) getEAVData = true;
		try {
			connection = daoFactory.getConnection();
			statement = connection.prepareStatement(finalSQL);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Tissue tissue = null;
				if (getEAVData) {
					tissue = mapTissueWithEAVData(resultSet, eavVariablesNames, getQuantityLeft);
				}
				else {
					tissue = mapTissue(resultSet);
				}
				tissues.add(tissue);
			}
			return tissues;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
	}

	/**
	 * Returns a list of tissues from the database (with data from the E.A.V.) ordered by chcID and
	 * tissueTypeID. The list is never null and is empty when the database does not contain any
	 * tissue.
	 * 
	 * @param getQuantityLeft
	 *            a boolean that indicates if the method should fetch the quantity left for each
	 *            tissue
	 * @param eavVariablesNames
	 *            a list of the EAV variables that should be retrieved for each tissue.
	 * @param tissuesOfInterest
	 *            a restrictive list of the Tissues that are to be fetched, Tissues not in this list
	 *            will be ignored
	 * @param showTissuesWithNoModalities
	 *            a Boolean that specifies wether the query should fetch tissues for which there are
	 *            no modalities for the variables specified in eavVariablesNames
	 * @return
	 * @throws DAOException
	 */
	public List<Tissue> listWithDynamicEAVQuery2(Boolean getQuantityLeft,
		ArrayList<String> eavVariablesNames, ArrayList<Tissue> tissuesOfInterest,
		Boolean showTissuesWithNoModalities) throws DAOException {

		String finalSQL = SQL_LIST_WITH_EAV_DATA_DYNAMIC2_SELECT_STUB;
		for (String eavVarName : eavVariablesNames) {
			finalSQL +=
				" , GROUP_CONCAT(DISTINCT IF(EAV_Variables_variableID= "
					+ "(SELECT variableID FROM EAV_Variables WHERE variableName = '" + eavVarName
					+ "'), " + "eav_modalities.modality,NULL )) AS " + eavVarName;
		}

		if (getQuantityLeft)
			finalSQL +=
				" , GROUP_CONCAT( DISTINCT CONCAT_WS(' on date : ',"
					+ "	TissueManipulation.quantityTissueLeftAfterManipulation  , "
					+ "	TissueManipulation.manipulationDate)"
					+ " ORDER BY manipulationDate DESC SEPARATOR ' / ') AS quantityLeft ";

		finalSQL += " FROM eav_modalities ";
		
		
		if(showTissuesWithNoModalities)
			finalSQL += " RIGHT OUTER JOIN Tissue ON " ;
		else
			finalSQL += " INNER JOIN Tissue ON " ;
			
		finalSQL +=
			" (tissue.chcid = `eav_modalities`.`tissue_chcID` "
				+ " AND tissue.`tissueTypeID` = `eav_modalities`.`tissue_tissueTypeID` )";

		if (getQuantityLeft)
			finalSQL +=
				" LEFT JOIN TissueManipulation "
					+ "  ON (eav_modalities.tissue_chcID = TissueManipulation.tissue_chcID "
					+ "  AND TissueManipulation.tissue_tissueTypeID = eav_modalities.tissue_tissueTypeID "
					+ "  AND quantityTissueLeftAfterManipulation IS NOT NULL "
					+ "  AND quantityTissueLeftAfterManipulation <> '') ";

		// FILTERING BY SPECIFIC TISSUES
		if (tissuesOfInterest != null && tissuesOfInterest.size() > 0) {
			finalSQL += " WHERE ( ";
			int tissueCount = 0;
			for (Tissue tissueOfInterest : tissuesOfInterest) {

				finalSQL +=
					" (eav_modalities.tissue_chcID = " + tissueOfInterest.getChcID()
						+ " AND eav_modalities.tissue_tissueTypeID = '"
						+ tissueOfInterest.getTissueTypeID() + "') ";
				if (tissueCount < tissuesOfInterest.size() - 1) finalSQL += " OR ";
				tissueCount++;
			}
			finalSQL += " ) ";
		}

//		if (showTissuesWithNoModalities) {
//			finalSQL += " GROUP BY tissue.chcID, tissue.tissueTypeID ";
//		}
//		else {
			finalSQL +=
				" GROUP BY tissue.chcID, tissue.tissueTypeID ";
//		}

//		System.err.println(finalSQL);
			
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Tissue> tissues = new ArrayList<Tissue>();
		boolean getEAVData = false;
		if (eavVariablesNames != null && eavVariablesNames.size() > 0) getEAVData = true;
		try {
			connection = daoFactory.getConnection();
			statement = connection.prepareStatement(finalSQL);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Tissue tissue = null;
				if (getEAVData) {
					tissue = mapTissueWithEAVData(resultSet, eavVariablesNames, getQuantityLeft);
				}
				else {
					tissue = mapTissue(resultSet);
				}
				tissues.add(tissue);
			}
			return tissues;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
	}

	/**
	 * Returns a list of tissues associated with the given tissue, this list is also set as a member
	 * value of the Tissue object, within this method
	 * 
	 * @param tissue
	 * @return
	 * @throws DAOException
	 */
	public List<Tissue> listAssociatedTissues(Tissue tissue) throws DAOException {
		if (tissue.getChcID() == null || tissue.getTissueTypeID() == null) {
			throw new IllegalArgumentException(
				"Tissue is not created yet, chcID or tissueTypeID not valid.");
		}
		if (tissue.isMasterTissue()) {
			return listMasterAssociatedTissues(tissue);
		}
		else {
			return listNonMasterAssociatedTissues(tissue);
		}

	}

	/**
	 * Returns a list of tissues (tumors and normal tissues) associated with the given tumor
	 * 
	 * @param tissue
	 * @return
	 * @throws DAOException
	 */
	private List<Tissue> listNonMasterAssociatedTissues(Tissue tissue) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Tissue> tissues = new ArrayList<Tissue>();
		try {
			connection = daoFactory.getConnection();
			// Object[] values = { tissue.getChcID(), tissue.getMasterTissue_chcID() };
			// Object[] values =
			// { tissue.getMasterTissue().getChcID(), tissue.getMasterTissue().getTissueTypeID(),
			// tissue.getMasterTissue().getChcID(), tissue.getMasterTissue().getTissueTypeID() };
			Object[] values =
				{ tissue.getMasterTissue().getChcID(), tissue.getMasterTissue().getTissueTypeID() };
			preparedStatement =
				prepareStatement(connection, SQL_LIST_NONMASTER_ASSOCIATED_TISSUES, false, values);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				tissues.add(mapTissue(resultSet));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
		tissues.remove(tissue);
		tissue.setAssociatedTissues(tissues);
		return tissues;
	}

	/**
	 * Returns a list of tissues (tumors and normal tissues) associated with the given normal tissue
	 * 
	 * @param tissue
	 * @return
	 * @throws DAOException
	 */
	private List<Tissue> listMasterAssociatedTissues(Tissue tissue) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Tissue> tissues = new ArrayList<Tissue>();
		try {
			connection = daoFactory.getConnection();
			Object[] values = { tissue.getChcID(), tissue.getTissueTypeID() };
			preparedStatement =
				prepareStatement(connection, SQL_LIST_MASTER_ASSOCIATED_TISSUES, false, values);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				tissues.add(mapTissue(resultSet));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
		tissues.remove(tissue);
		tissue.setAssociatedTissues(tissues);
		return tissues;
	}

	// private List<Tissue> listAssociatedTissues(Tissue tissue)

	/**
	 * Retrieves all the EAV data associated with the given tissue, stores it in a HashMap, and sets
	 * this hashmap to the given tissue.
	 * 
	 * @param tissue
	 *            The tissue of interest, whose clinical data is to be fetched from the EAV tables
	 *            of the database. The tissue clinicalData field is directly set within this method,
	 *            and therefore, no data is returned by this method.
	 * @throws DAOException
	 *             If something fails at database level.
	 */
	public void fetchTissueEAVData(Tissue tissue) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			Object[] values = { tissue.getChcID(), tissue.getTissueTypeID() };
			preparedStatement =
				prepareStatement(connection, SQL_FETCH_TISSUE_EAV_DATA, false, values);
			resultSet = preparedStatement.executeQuery();
			LinkedHashMap<String, EAVModality> eavData = processTissueEAVData(resultSet);
			tissue.setEavData(eavData);
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * Create the given Tissue in the database, as a master Tissue ()
	 * 
	 * @param tissue
	 * @throws DAOException
	 */
	public void createMaster(Tissue tissue) throws DAOException {
		Object[] values = { tissue.getChcID(), tissue.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement =
				prepareStatement(connection, SQL_INSERT_MASTERTISSUE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Master Tissue creation failed, no rows affected.");
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
	}

	public void createNonMaster(Tissue nonMasterTissue) throws DAOException {
		if (nonMasterTissue.getMasterTissue() == null
			|| nonMasterTissue.getMasterTissue().getChcID() == null
			|| nonMasterTissue.getMasterTissue().getTissueTypeID() == null) {
			throw new DAOException("The given tissue has no master tissue.");
		}
		Object[] values =
			{ nonMasterTissue.getChcID(), nonMasterTissue.getTissueTypeID(),
				nonMasterTissue.getMasterTissue().getChcID(),
				nonMasterTissue.getMasterTissue().getTissueTypeID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement =
				prepareStatement(connection, SQL_INSERT_NONMASTER_TISSUE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Non Master tissue creation failed, no rows affected.");
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
	}

	public void update(Tissue tissue) throws DAOException {
		if (tissue.getChcID() == null || tissue.getTissueTypeID() == null) {
			throw new IllegalArgumentException(
				"Tissue is not created yet, chcID or tissueTypeID not valid.");
		}
		Object[] values =
			{ tissue.getMasterTissue() != null ? tissue.getMasterTissue().getChcID()
				: null,
				tissue.getMasterTissue() != null ? tissue.getMasterTissue().getTissueTypeID()
					: null, tissue.getTissueComment(), tissue.getPatient_patientID(),
				tissue.getHistologicDiagnosis_HistologicDiagnosisID(), tissue.getChcID(),
				tissue.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_UPDATE_TISSUE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Updating Tissue failed, no rows affected.");
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
	}

	public void save(Tissue tissue) throws DAOException {
		if (!exists(tissue)) {
			if (tissue.isMasterTissue())
				createMaster(tissue);
			else
				createNonMaster(tissue);
		}
		else
			update(tissue);
	}

	public void delete(Tissue tissue) throws DAOException {
		if (tissue.getChcID() == null || tissue.getTissueTypeID() == null) {
			throw new IllegalArgumentException(
				"Tissue is not created yet, chcID or tissueTypeID not valid.");
		}
		Object[] values = { tissue.getChcID(), tissue.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_DELETE_TISSUE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Deleting Tissue failed, no rows affected.");
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
	}

	/**
	 * 
	 * @param tissue
	 * @throws DAOException
	 */
	public void fetchTissueQuantityAfterLastManipulation(Tissue tissue) throws DAOException {
		if (tissue.getChcID() == null || tissue.getTissueTypeID() == null) {
			throw new IllegalArgumentException(
				"Tissue is not created yet, chcID or tissueTypeID not valid.");
		}
		if (!(existsCHCID(tissue.getChcID()) && existsTissueTypeID(tissue.getTissueTypeID()))) {
			throw new IllegalArgumentException("tissue does not exist in the database.");
		}
		Object[] values = { tissue.getChcID(), tissue.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement =
				prepareStatement(connection, SQL_GET_TISSUE_QUANTITY, false, values);
			resultSet = preparedStatement.executeQuery();

			ArrayList<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
			while (resultSet.next()) {
				HashMap<String, Object> row = new HashMap<String, Object>();
				if (resultSet.getString("quantityTissueLeftAfterManipulation") != null
					&& resultSet.getString("quantityTissueLeftAfterManipulation") != "") {
					row.put("quantityTissueLeftAfterManipulation", resultSet
						.getString("quantityTissueLeftAfterManipulation"));
					row.put("dateExtraction", resultSet.getDate("manipulationDate"));
					result.add(row);
				}
			}
			if (result.size() == 1) {
				tissue.setQuantityLeft((String) result.get(0).get(
					"quantityTissueLeftAfterManipulation"));
			}
			else if (result.size() > 1) {
				Date latestDate = new Date(0);
				String latestQuantity = new String();
				for (HashMap<String, Object> row : result) {
					if (row.get("dateExtraction") != null
						&& ((Date) row.get("dateExtraction")).after(latestDate)) {
						latestDate = (Date) row.get("dateExtraction");
						latestQuantity = (String) row.get("quantityTissueLeftAfterManipulation");
					}
				}
				tissue.setQuantityLeft(latestQuantity);
			}
			else {
				// TODO Sale !!
				tissue.setQuantityLeft("unknown");
			}

		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * Returns true if the given chcID exists in the database.
	 * 
	 * @param chcID
	 *            the chcID which is to be checked in the database.
	 * @return True if the given chcID exists in the database.
	 * @throws DAOException
	 */
	public boolean existsCHCID(Integer chcID) throws DAOException {
		return exists(SQL_EXISTS_CHCID, chcID);
	}

	/**
	 * Returns true if the given tissueTypeID exists in the database.
	 * 
	 * @param tissueTypeID
	 *            the tissueTypeID which is to be checked in the database.
	 * @return True if the given tissueTypeID exists in the database.
	 * @throws DAOException
	 */
	public boolean existsTissueTypeID(String tissueTypeID) throws DAOException {
		return exists(SQL_EXISTS_TISSUETYPEID, tissueTypeID);
	}

	/**
	 * Returns true if the given tissue exists in the database
	 * 
	 * @param tissue
	 * @return
	 * @throws DAOException
	 */
	public boolean exists(Tissue tissue) throws DAOException {
		return exists(SQL_EXISTS, tissue.getChcID(), tissue.getTissueTypeID());
	}

	/**
	 * Returns true if the given COUNT sql query with the given values returns a value greater than
	 * 0.
	 * 
	 * @param sql
	 *            The SQL COUNT query to be executed in the database.
	 * @param values
	 *            The PreparedStatement values to be set.
	 * @return True if the given SQL COUNT query with the given values returns a value greater than
	 *         0.
	 * @throws DAOException
	 */
	private boolean exists(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		boolean exists = false;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, sql, false, values);
			resultSet = statement.executeQuery();
			if (resultSet.next()) exists = resultSet.getInt(1) > 0;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}

		return exists;
	}

	/**
	 * For a given tissue (which is supposedly a master tissue), removes all references to it in the
	 * table Tissue.
	 * 
	 * @param masterTissue
	 * @return the number of rows in which a reference to this tissue was removed, can be 0 if the
	 *         tissue given wasn't a master tissue
	 * @throws DAOException
	 */
	public Integer dissociateMasterTissue(Tissue masterTissue) throws DAOException {
		Object[] values = { masterTissue.getChcID(), masterTissue.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement statement = null;
		int affectedRows = 0;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_MASTERTISSUE_DISSOCIATE, false, values);
			affectedRows = statement.executeUpdate();
			return affectedRows;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
	}

	// Helpers
	// --------------------------------------------------------------------------------------------

	/**
	 * Map the current row of the given ResultSet to a Tissue.
	 * 
	 * @param resultSet
	 *            The ResultSet of which the current row is to be mapped to a Tissue.
	 * @return The mapped Tissue from the current row of the given ResultSet.
	 * @throws SQLException
	 *             If something fails at database level.
	 */
	private static Tissue mapTissue(ResultSet resultSet) throws SQLException {
		Tissue masterTissue = null;
		if (resultSet.getInt("Tissue.masterTissue_chcID") > 0
			&& resultSet.getString("Tissue.masterTissue_tissueTypeID") != null
			&& resultSet.getString("Tissue.masterTissue_tissueTypeID").length() > 0) {
			masterTissue =
				new Tissue(resultSet.getInt("Tissue.masterTissue_chcID"), resultSet
					.getString("Tissue.masterTissue_tissueTypeID"));
		}
		return new Tissue(resultSet.getInt("Tissue.chcID"), resultSet
			.getString("Tissue.tissueTypeID"), masterTissue, resultSet
			.getInt("Tissue.patient_patientID"), resultSet
			.getInt("Tissue.histologicDiagnosis_HistologicDiagnosisID"), resultSet
			.getString("Tissue.tissueComment"));
	}

	/**
	 * @param resultSet
	 * @param eavVariablesNames
	 * @param getQuantityLeft
	 *            TODO
	 * @return
	 * @throws SQLException
	 */
	private static Tissue mapTissueWithEAVData(ResultSet resultSet,
		ArrayList<String> eavVariablesNames, Boolean getQuantityLeft) throws SQLException {
		Tissue masterTissue = null;
		if (resultSet.getInt("masterTissue_chcID") > 0
			&& resultSet.getString("masterTissue_tissueTypeID") != null
			&& resultSet.getString("masterTissue_tissueTypeID").length() > 0) {
			masterTissue =
				new Tissue(resultSet.getInt("masterTissue_chcID"), resultSet
					.getString("masterTissue_tissueTypeID"));
		}

		Tissue tissue =
			new Tissue(resultSet.getInt("chcID"), resultSet.getString("tissueTypeID"), masterTissue);

		LinkedHashMap<String, EAVModality> eavData = new LinkedHashMap<String, EAVModality>();
		for (String variableName : eavVariablesNames) {
			EAVModality eavModality =
				new EAVModality(new EAVVariable(variableName), tissue, resultSet
					.getString(variableName));
			eavData.put(variableName, eavModality);
		}

		tissue.setEavData(eavData);

		if (getQuantityLeft) {
			String quantityLeft = resultSet.getString("quantityLeft");
			tissue.setQuantityLeft(quantityLeft);
		}

		return tissue;
	}

	/**
	 * Processes a ResultSet that contains all the EAV data (fetched from the EAV tables of the
	 * database) for a single tissue, and returns the EAV data in a HashMap.
	 * 
	 * @param resultSet
	 *            the ResultSet that contains all EAV data for a given tissue
	 * @return the HashMap that contains all the clinical data retrieved in the resultset, provided
	 *         the data is relevant (i.e. not deprecated). This HashMap has yet to be set to a
	 *         tissue (using the setClinicalData() setter)
	 * @throws SQLException
	 *             If something fails at database level
	 * @throws DAOException
	 */
	private static LinkedHashMap<String, EAVModality> processTissueEAVData(ResultSet resultSet)
		throws SQLException, DAOException {
		LinkedHashMap<String, EAVModality> eavData = new LinkedHashMap<String, EAVModality>();
		while (resultSet.next()) {
			EAVModality eavModality = new EAVModality();

			String dataType = resultSet.getString("dataType");
			String humanReadableName = resultSet.getString("humanReadableName");
			String modality = resultSet.getString("modality");
			String variableName = resultSet.getString("variableName");
			boolean deprecatedModality = resultSet.getInt("isDeprecated") != 0;
			boolean deprecatedVariable = resultSet.getInt("deprecatedVariable") != 0;

			EAVVariable eavVariable = new EAVVariable(variableName);
			eavVariable.setHumanReadableName(humanReadableName);
			eavVariable.setDataType(dataType);

			eavModality.setDeprecated(deprecatedModality);
			eavModality.setModality(modality);
			eavModality.setEavVariable(eavVariable);

			if (!deprecatedModality && !deprecatedVariable) {

				eavData.put(variableName, eavModality);

			}
		}
		return eavData;
	}

}
